package com.asdan.seededu.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.asdan.seededu.entity.security.School;
import com.asdan.seededu.entity.security.User;
import com.asdan.seededu.entity.security.Userdata;
import com.asdan.seededu.service.ProjectService;
import com.asdan.seededu.service.SilkroadProjectService;



public class SilkroadProjectServiceImpl extends UtilImpl implements SilkroadProjectService{

	public void uploadXxqr(File xxqr ,String stepid) throws IOException {
		// TODO Auto-generated method stub
			InputStream is = new FileInputStream(xxqr);
	        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
	        List<User> list = new ArrayList<User>();
	        // 循环工作表Sheet
	        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
	            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
	            if (hssfSheet == null) {
	                continue;
	            }
	            // 循环行Row
	            for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
	                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
	                if (hssfRow == null) {
	                    continue;
	                }
	                User user= new User();
	                // 循环列Cell
	                // 0学号 1姓名 2学院 3课程名 4 成绩
	                // for (int cellNum = 0; cellNum <=4; cellNum++) {
	                HSSFCell cell = hssfRow.getCell(0);
	                if (cell == null) {
	                    continue;
	                }
	                String status = getValue(cell);
	                if("0".equals(status)){
	                	cell = hssfRow.getCell(1);
	 	                if (cell == null) {
	 	                    continue;
	 	                }
	 	                String uid = getValue(cell);
	 	                user.setUid(Integer.parseInt(uid)) ;
	 	                
	 	                
	 	               cell = hssfRow.getCell(2);
	 	                if (cell == null) {
	 	                    continue;
	 	                }
	 	                user.setName(getValue(cell)) ;
	 	               cell = hssfRow.getCell(3);
	 	                if (cell == null) {
	 	                    continue;
	 	                }
	 	                user.setSex(getValue(cell)) ;
	 	               cell = hssfRow.getCell(4);
	 	                if (cell == null) {
	 	                    continue;
	 	                }
	 	                user.setFirstName(getValue(cell)) ;
	 	               cell = hssfRow.getCell(5);
	 	                if (cell == null) {
	 	                    continue;
	 	                }
	 	                user.setLastName(getValue(cell)) ;
	 	               cell = hssfRow.getCell(6);
	 	                if (cell == null) {
	 	                    continue;
	 	                }
	 	                user.setBirthday(Integer.parseInt(getValue(cell))) ;
	 	               cell = hssfRow.getCell(7);
	 	                if (cell == null) {
	 	                    continue;
	 	                }
	 	                user.setSchool(new School((getValue(cell)))) ;
	 	               cell = hssfRow.getCell(8);
	 	                if (cell == null) {
	 	                    continue;
	 	                }
	 	                user.setGrade(getValue(cell)) ;
	 	               cell = hssfRow.getCell(9);
	 	                if (cell == null) {
	 	                    continue;
	 	                }
	 	                user.setEmail(getValue(cell)) ;
	 	               cell = hssfRow.getCell(10);
	 	                if (cell == null) {
	 	                    continue;
	 	                }
	 	                user.setCellPhone(getValue(cell)) ;
	 	               cell = hssfRow.getCell(11);
	 	                if (cell == null) {
	 	                    continue;
	 	                }
	 	                user.setQQ(getValue(cell)) ;
	 	               cell = hssfRow.getCell(12);
	 	                if (cell == null) {
	 	                    continue;
	 	                }
	 	                user.setAddress(getValue(cell)) ;
	 	               cell = hssfRow.getCell(13);
	 	                if (cell == null) {
	 	                    continue;
	 	                }
	 	                user.setPostCode(getValue(cell)) ;
	 	               cell = hssfRow.getCell(14);
	 	                if (cell == null) {
	 	                    continue;
	 	                }
	 	                user.setFatherName(getValue(cell)) ;
	 	               cell = hssfRow.getCell(15);
	 	                if (cell == null) {
	 	                    continue;
	 	                }
	 	                user.setFatherEmail(getValue(cell)) ;
	 	               cell = hssfRow.getCell(16);
	 	                if (cell == null) {
	 	                    continue;
	 	                }
	 	                user.setFatherPhone(getValue(cell)) ;
	 	               cell = hssfRow.getCell(17);
	 	                if (cell == null) {
	 	                    continue;
	 	                }
	 	                user.setMotherName(getValue(cell)) ;
	 	               cell = hssfRow.getCell(18);
	 	                if (cell == null) {
	 	                    continue;
	 	                }
	 	                user.setMotherEmail(getValue(cell)) ;
	 	                //少了一个 帮我看看
	 	               new ProjectServiceImpl().updateUserdata(Integer.parseInt(stepid), Integer.parseInt(uid),user) ;
	                	
	                }else if("1".equals(status)){
	                	cell = hssfRow.getCell(1);
	 	                if (cell == null) {
	 	                    continue;
	 	                }
	 	               String uid = getValue(cell);
	                	new ProjectServiceImpl().toNextStep(Integer.parseInt(stepid), Integer.parseInt(uid)) ;
	                }
	                
	            }
	        }
		
	}

	public File downloadXxqr(String stepid, String filepath) {
		System.out.println(stepid);
		System.out.println(filepath);
		// TODO Auto-generated method stub
		ProjectService ps  =  new ProjectServiceImpl() ;
		List<Userdata>  userList = ps.getUsersbyStepid(stepid) ;
		int CountColumnNum = userList.size();
        // 创建Excel文档
        HSSFWorkbook hwb = new HSSFWorkbook();
        // sheet 对应一个工作页
        HSSFSheet sheet = hwb.createSheet("信息确认");
        
        HSSFRow firstrow = sheet.createRow(0); // 下标为0的行开始
        HSSFCell[] firstcell = new HSSFCell[CountColumnNum];
        String[] names = new String[19];
        names[0] = "流程状态" ; 
        names[1] = "ID";
        names[2] = "姓名";
        names[3] = "FIRST NAME";
        names[4] = "LAST NAME";
        names[5] = "生日";
        names[6] = "学校" ; 
        names[7] = "年级";
        names[8] = "邮箱";
        names[9] = "手机号";
        names[10] = "QQ";
        names[11] = "通信地址";
        names[12] = "邮编" ; 
        names[13] = "父亲姓名";
        names[14] = "邮箱";
        names[15] = "手机";
        names[16] = "母亲姓名";
        names[17] = "邮箱";
        names[18] = "手机";
        for (int j = 0; j < 19; j++) {
            firstcell[j] = firstrow.createCell(j);
            firstcell[j].setCellValue(new HSSFRichTextString(names[j]));
            sheet.setColumnWidth(j, 30*256) ;
        }
        for (int i = 0; i < userList.size(); i++) {
            // 创建一行
            HSSFRow row = sheet.createRow(i + 1);
            // 得到要插入的每一条记录
            Userdata ud = userList.get(i);
            for (int colu = 0; colu <= 18; colu++) {
                // 在一行内循环
            	User user =  ud.getUser() ;
                HSSFCell cell = row.createCell(0);
                
                cell.setCellValue(ud.getStepstatus());
                cell = row.createCell(1);
                cell.setCellValue(user.getUid());
                cell = row.createCell(2);
                cell.setCellValue(user.getName());
                cell = row.createCell(3);
                cell.setCellValue(user.getFirstName());
                cell = row.createCell(4);
                cell.setCellValue(user.getLastName());
                
                cell = row.createCell(5);
                cell.setCellValue(user.getBirthday());
                cell = row.createCell(6);
                cell.setCellValue(user.getSchool().getSchoolNAME());
                cell = row.createCell(7);
                cell.setCellValue(user.getGrade());
                cell = row.createCell(8);
                cell.setCellValue(user.getEmail());
                cell = row.createCell(9);
                cell.setCellValue(user.getCellPhone());
                cell = row.createCell(10);
                cell.setCellValue(user.getQQ());
                cell = row.createCell(11);
                cell.setCellValue(user.getAddress());
                cell = row.createCell(12);
                cell.setCellValue(user.getPostCode());
                cell = row.createCell(13);
                cell.setCellValue(user.getFatherName());
                cell = row.createCell(14);
                cell.setCellValue(user.getFatherEmail());
                cell = row.createCell(15);
                cell.setCellValue(user.getFatherPhone());
                cell = row.createCell(16);
                cell.setCellValue(user.getMotherName());
                cell = row.createCell(17);
                cell.setCellValue(user.getMotherEmail());
                cell = row.createCell(18);
                cell.setCellValue(user.getMotherPhone());
            }
        }
        // 创建文件输出流，准备输出电子表格
        OutputStream out;
		try {
			 out = new FileOutputStream(filepath);
			 hwb.write(out);
		     out.close();
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
       
        System.out.println("数据库导出成功");
		return new File(filepath);
	}
	  @SuppressWarnings("static-access")
	    private String getValue(HSSFCell hssfCell) {
	        if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
	            // 返回布尔类型的值
	            return String.valueOf(hssfCell.getBooleanCellValue());
	        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
	            // 返回数值类型的值
	            return String.valueOf(hssfCell.getNumericCellValue());
	        } else {
	            // 返回字符串类型的值
	            return String.valueOf(hssfCell.getStringCellValue());
	        }
	    }
	
	

}
